package com.express.e.root.dao.impl;

import com.express.e.root.bean.Delivery;
import com.express.e.root.dao.DeliveryDao;
import com.express.e.root.utils.DBUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@SuppressWarnings("all")
public class DeliveryDaoImpl extends DBUtils implements DeliveryDao {
    @Override
    public Boolean addDelivery(Delivery delivery) {
        Integer addNum = 0;
        String sql = "INSERT INTO delivery (deliveryCode,deliveryCompany,deliveryAcceptor,acceptorPhone,deliveryNum,deliveryState,stateChangeTime) VALUES(?,?,?,?,?,?,?);";
        List params = new ArrayList<>();
        params.add(delivery.getDeliveryCode());
        params.add(delivery.getDeliveryCompany());
        params.add(delivery.getDeliveryAcceptor());
        params.add(delivery.getAcceptorPhone());
        params.add(delivery.getDeliveryNum());
        params.add(delivery.getDeliveryState());
        params.add(delivery.getStateChangeTime());
        try {
            addNum = update(sql, params);
        } finally {
            closeAll();
        }
        return addNum > 0;
    }

    @Override
    public Boolean deleteDelivery(String deliveryNum) {
        Integer delNum = 0;
        String sql = "UPDATE delivery AS d SET d.`isDeleted` = 1 WHERE d.`deliveryNum` = ?;";
        List params = new ArrayList<>();
        params.add(deliveryNum);
        try {
            delNum = update(sql, params);
        } finally {
            closeAll();
        }
        return delNum > 0;
    }

    @Override
    public Boolean fetchDelivery(String deliveryNum) {
        Integer fetNum = 0;
        String sql = "UPDATE delivery AS d SET d.`deliveryState` = 1 WHERE d.`deliveryNum` = ?;";
        List params = new ArrayList<>();
        params.add(deliveryNum);
        try {
            fetNum = update(sql, params);
        } finally {
            closeAll();
        }
        return fetNum > 0;
    }

    @Override
    public List<Delivery> queryByList(int pageIndex, int pageSize) {
        List deliverys = new ArrayList<>();
        String sql = "select * from delivery where 1=1 and isDeleted = 0  limit ?,?";
        List params = new ArrayList<>();
        params.add(pageIndex);
        params.add(pageSize);

        try {
            resultSet = query(sql, params);
            while (resultSet.next()) {
                Delivery delivery = new Delivery();
                delivery.setDeliveryId(resultSet.getInt("deliveryId"));
                delivery.setDeliveryCode(resultSet.getString("deliveryCode"));
                delivery.setDeliveryCompany(resultSet.getString("deliveryCompany"));
                delivery.setDeliveryAcceptor(resultSet.getString("deliveryAcceptor"));
                delivery.setAcceptorPhone(resultSet.getString("acceptorPhone"));
                delivery.setDeliveryNum(resultSet.getString("deliveryNum"));
                delivery.setDeliveryState(resultSet.getInt("deliveryState"));
                delivery.setStateChangeTime(resultSet.getDate("stateChangeTime"));
                deliverys.add(delivery);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return deliverys;
    }

    @Override
    public Boolean updateDelivery(Delivery delivery) {
        Integer updateNum = 0;
        String sql = "UPDATE delivery AS d SET d.`deliveryCode` = ?,d.`deliveryCompany`= ?,d.`deliveryAcceptor`=?,d.`acceptorPhone`=?,d.`deliveryNum`=?,d.`deliveryState`=?,d.`stateChangeTime`=? WHERE d.`deliveryId` = ?;";
        List params = new ArrayList<>();
        params.add(delivery.getDeliveryCode());
        params.add(delivery.getDeliveryCompany());
        params.add(delivery.getDeliveryAcceptor());
        params.add(delivery.getAcceptorPhone());
        params.add(delivery.getDeliveryNum());
        params.add(delivery.getDeliveryState());
        params.add(delivery.getStateChangeTime());
        params.add(delivery.getDeliveryId());
        try {
            updateNum = update(sql, params);
        } finally {
            closeAll();
        }
        return updateNum == 1;
    }

    @Override
    public Delivery queryById(int deliveryId) {
        Delivery delivery = new Delivery();
        String sql = "SELECT * FROM delivery WHERE isDeleted = 0 and deliveryId = ?;";
        List params = new ArrayList<>();
        params.add(deliveryId);
        try {
            resultSet = query(sql, params);
            while (resultSet.next()) {
                delivery.setDeliveryId(resultSet.getInt("deliveryId"));
                delivery.setDeliveryCode(resultSet.getString("deliveryCode"));
                delivery.setDeliveryCompany(resultSet.getString("deliveryCompany"));
                delivery.setDeliveryAcceptor(resultSet.getString("deliveryAcceptor"));
                delivery.setAcceptorPhone(resultSet.getString("acceptorPhone"));
                delivery.setDeliveryNum(resultSet.getString("deliveryNum"));
                delivery.setDeliveryState(resultSet.getInt("deliveryState"));
                delivery.setStateChangeTime(resultSet.getDate("stateChangeTime"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return delivery;
    }

    @Override
    public Integer total() {
        int total = 0;
        String sql = "select count(*) as total from delivery where isDeleted = 0;";

        try {
            resultSet = query(sql, null);
            while (resultSet.next()) {
                total = resultSet.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public Boolean codeIsRepeated(Integer code) {
        int repeatNum = 0;
        String sql = "SELECT COUNT(*)AS num FROM delivery WHERE isDeleted = 0 and deliveryCode = ?;";
        List params = new ArrayList<>();
        params.add(code);

        try {
            resultSet = query(sql, params);
            while (resultSet.next()) {
                repeatNum = resultSet.getInt("num");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return repeatNum != 0;
    }

    @Override
    public Delivery queryByCode(Integer code) {
        Delivery delivery = new Delivery();
        String sql = "SELECT * FROM delivery WHERE deliveryCode = ? and isDeleted = 0;";
        ArrayList<Object> params = new ArrayList<>();
        params.add(code);

        try {
            resultSet = query(sql, params);
            while (resultSet.next()) {
                delivery.setDeliveryId(resultSet.getInt("deliveryId"));
                delivery.setDeliveryCode(resultSet.getString("deliveryCode"));
                delivery.setDeliveryCompany(resultSet.getString("deliveryCompany"));
                delivery.setDeliveryAcceptor(resultSet.getString("deliveryAcceptor"));
                delivery.setAcceptorPhone(resultSet.getString("acceptorPhone"));
                delivery.setDeliveryNum(resultSet.getString("deliveryNum"));
                delivery.setDeliveryState(resultSet.getInt("deliveryState"));
                delivery.setStateChangeTime(resultSet.getDate("stateChangeTime"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return delivery;
    }

    @Override
    public List<Delivery> queryAll() {
        List deliverys = new ArrayList<>();
        String sql = "select * from delivery where 1=1 and isDeleted = 0";
        try {
            resultSet = query(sql, null);
            while (resultSet.next()) {
                Delivery delivery = new Delivery();
                delivery.setDeliveryId(resultSet.getInt("deliveryId"));
                delivery.setDeliveryCode(resultSet.getString("deliveryCode"));
                delivery.setDeliveryCompany(resultSet.getString("deliveryCompany"));
                delivery.setDeliveryAcceptor(resultSet.getString("deliveryAcceptor"));
                delivery.setAcceptorPhone(resultSet.getString("acceptorPhone"));
                delivery.setDeliveryNum(resultSet.getString("deliveryNum"));
                delivery.setDeliveryState(resultSet.getInt("deliveryState"));
                delivery.setStateChangeTime(resultSet.getDate("stateChangeTime"));
                deliverys.add(delivery);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return deliverys;
    }


    @Override
    public Delivery queryByNum(String deliveryNum) {
        Delivery delivery = new Delivery();
        String sql = "SELECT * FROM delivery WHERE deliveryNum = ? and isDeleted = 0;";
        ArrayList<Object> params = new ArrayList<>();
        params.add(deliveryNum);

        try {
            resultSet = query(sql, params);
            while (resultSet.next()) {
                delivery.setDeliveryId(resultSet.getInt("deliveryId"));
                delivery.setDeliveryCode(resultSet.getString("deliveryCode"));
                delivery.setDeliveryCompany(resultSet.getString("deliveryCompany"));
                delivery.setDeliveryAcceptor(resultSet.getString("deliveryAcceptor"));
                delivery.setAcceptorPhone(resultSet.getString("acceptorPhone"));
                delivery.setDeliveryNum(resultSet.getString("deliveryNum"));
                delivery.setDeliveryState(resultSet.getInt("deliveryState"));
                delivery.setStateChangeTime(resultSet.getDate("stateChangeTime"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return delivery;
    }

    @Override
    public Delivery queryByName(String deliveryName) {
        Delivery delivery = new Delivery();
        String sql = "SELECT * FROM delivery WHERE deliveryAcceptor = ? and isDeleted = 0;";
        ArrayList<Object> params = new ArrayList<>();
        params.add(deliveryName);
        try {
            resultSet = query(sql, params);
            while (resultSet.next()) {
                delivery.setDeliveryId(resultSet.getInt("deliveryId"));
                delivery.setDeliveryCode(resultSet.getString("deliveryCode"));
                delivery.setDeliveryCompany(resultSet.getString("deliveryCompany"));
                delivery.setDeliveryAcceptor(resultSet.getString("deliveryAcceptor"));
                delivery.setAcceptorPhone(resultSet.getString("acceptorPhone"));
                delivery.setDeliveryNum(resultSet.getString("deliveryNum"));
                delivery.setDeliveryState(resultSet.getInt("deliveryState"));
                delivery.setStateChangeTime(resultSet.getDate("stateChangeTime"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return delivery;
    }

    @Override
    public Delivery queryByPhone(String phone) {
        Delivery delivery = new Delivery();
        String sql = "SELECT * FROM delivery WHERE acceptorPhone = ? and isDeleted = 0;";
        ArrayList<Object> params = new ArrayList<>();
        params.add(phone);
        try {
            resultSet = query(sql, params);
            while (resultSet.next()) {
                delivery.setDeliveryId(resultSet.getInt("deliveryId"));
                delivery.setDeliveryCode(resultSet.getString("deliveryCode"));
                delivery.setDeliveryCompany(resultSet.getString("deliveryCompany"));
                delivery.setDeliveryAcceptor(resultSet.getString("deliveryAcceptor"));
                delivery.setAcceptorPhone(resultSet.getString("acceptorPhone"));
                delivery.setDeliveryNum(resultSet.getString("deliveryNum"));
                delivery.setDeliveryState(resultSet.getInt("deliveryState"));
                delivery.setStateChangeTime(resultSet.getDate("stateChangeTime"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return delivery;
    }

    @Override
    public List queryListByName(String name) {
        List deliverys = new ArrayList<>();
        String sql = "select * from delivery where 1=1 and isDeleted = 0 and deliveryAcceptor = ?";
        List params = new ArrayList<>();
        params.add(name);
        try {
            resultSet = query(sql, params);
            while (resultSet.next()) {
                Delivery delivery = new Delivery();
                delivery.setDeliveryId(resultSet.getInt("deliveryId"));
                delivery.setDeliveryCode(resultSet.getString("deliveryCode"));
                delivery.setDeliveryCompany(resultSet.getString("deliveryCompany"));
                delivery.setDeliveryAcceptor(resultSet.getString("deliveryAcceptor"));
                delivery.setAcceptorPhone(resultSet.getString("acceptorPhone"));
                delivery.setDeliveryNum(resultSet.getString("deliveryNum"));
                delivery.setDeliveryState(resultSet.getInt("deliveryState"));
                delivery.setStateChangeTime(resultSet.getDate("stateChangeTime"));
                deliverys.add(delivery);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return deliverys;
    }

    @Override
    public List queryListByCourier(String courierName) {
        List deliverys = new ArrayList<>();
        String sql = "select * from delivery as d where 1=1 and isDeleted = 0 and d.`insertCourier` = ?";
        List params = new ArrayList<>();
        params.add(courierName);
        try {
            resultSet = query(sql, params);
            while (resultSet.next()) {
                Delivery delivery = new Delivery();
                delivery.setDeliveryId(resultSet.getInt("deliveryId"));
                delivery.setDeliveryCode(resultSet.getString("deliveryCode"));
                delivery.setDeliveryCompany(resultSet.getString("deliveryCompany"));
                delivery.setDeliveryAcceptor(resultSet.getString("deliveryAcceptor"));
                delivery.setAcceptorPhone(resultSet.getString("acceptorPhone"));
                delivery.setDeliveryNum(resultSet.getString("deliveryNum"));
                delivery.setDeliveryState(resultSet.getInt("deliveryState"));
                delivery.setStateChangeTime(resultSet.getDate("stateChangeTime"));
                delivery.setInsertCourier(resultSet.getString("insertCourier"));
                deliverys.add(delivery);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return deliverys;
    }


}
